import pymysql
import pandas as pd
import sys
class CURD:
    def __init__(self,table=None):
        self.conn = pymysql.connect(host='113.31.110.212', user='root', password='zzh0117.', database='db2', charset='utf8')
        self.cursor = self.conn.cursor()

        self.table = table

    def parse_file(self,path):
        try:
            data = pd.read_csv(path,encoding='utf-8')
            cols = data.columns
            if 'sname' in cols:
                self.table = 'student'
            elif 'tname' in cols:
                self.table = 'teacher'
            if self.table == 'student':
                sql = f"insert into {self.table}(sname,age,dept,password,role,snum,major,classnum,email,tel,qq) VALUES "
                for i in range(len(data)):
                    sql += f"('{data['sname'][i]}',{data['age'][i]},'{data['dept'][i]}','{data['password'][i]}','student','{data['snum'][i]}','{data['major'][i]}',{data['classnum'][i]},'{data['email'][i]}','{data['tel'][i]}','{data['qq'][i]}'),"
                sql = sql[:-1]
                print(sql)
                self.cursor.execute(sql)
                self.conn.commit()
            elif self.table == 'teacher':
                sql = f"insert into {self.table}(tname,age,dept,password,role,tnum,email,tel,qq,ttile,location,project_num) VALUES"
                for i in range(len(data)):
                    sql += f"('{data['tname'][i]}',{data['age'][i]},'{data['dept'][i]}','{data['password'][i]}','{data['role'][i]}','{data['tnum'][i]}','{data['email'][i]}','{data['tel'][i]}','{data['qq'][i]}','{data['title'][i]}'," \
                           f"'{data['office'][i]}',{data['project_num'][i]}),"
                sql = sql[:-1]
                self.cursor.execute(sql)
                self.conn.commit()
        except Exception as e:
            print(e)
            exit(-1)

if __name__ == '__main__':
    args = sys.argv
    path = args[1]
    curd = CURD()
    curd.parse_file(path)
    exit(0)
